Show AllYou can control how the data in a PivotTable list is summarized by customizing the layout. A different layout lets you calculate and compare summarized values for different elements in your data, or display summaries for a subset of the data. When you make the PivotTable list available to users, users can make further changes to your initial layout.
You change the layout by moving the fields to predefined drop areas within the PivotTable list.
Drop areas
Detail area
After you move fields to the drop areas so that the drop area captions are covered up, you can still drag additional fields to the areas. You can also use the Add to button in the field list to add fields to different drop areas.
Moving fields to the row and column areas
When you move a field to the row area, the unique items of data within the field are displayed down the rows of the PivotTable list. When you move a field to the column area, the unique items of data are displayed across the columns.
Moving fields to the detail area
To view all of the detail records from the source data for the PivotTable list, you can move fields to the detail area. If you don't want to rearrange the fields or change the summarized data, you can view details alongside the summarized data.
Moving fields to the data area
Data fields provide the values to be summarized in the PivotTable list. When you move a field to the data area, the values from the field are summarized for the row and column field items.
Moving fields to the filter area
A filter field is similar to a page field in a Microsoft Excel PivotTable report. Filter fields allow you to confine the view to a particular part of the available data. For example, when you move a Product field to the filter area, you can have the PivotTable list display a summary for one product at a time.
Moving row and column fields to a higher or lower level
When a PivotTable list has multiple row and column fields, the fields that are closest to the summarized data are referred to as inner fields. The other fields are outer fields.
Outer row field
Inner row field
Outer field items
Items for the inner field are repeated for each item in the outer field.
The layout of a PivotTable list does not have to include all of the fields that are available from the source data. If more data is available, you can add fields to the PivotTable list layout. For example, if the PivotTable list summarizes sales revenue, and the source data also includes sales quantities, you might add the Quantity field as a data field to summarize both revenue and quantity of products sold. You can also remove fields from the PivotTable list layout that you no longer want to see.
Restrictions on moving and adding fields
If you work with a PivotTable list that is based on source data from an OLAP database, such as one created with Microsoft SQL Server OLAP Services, you'll notice that you can't move fields to the detail area. You'll also notice that some fields can be moved only to the row, column, and filter areas, and that fields in the data area cannot be moved to other areas.
Restricting use of a PivotTable list in the browser
You can restrict the user's ability to change the layout of the PivotTable list by preventing fields from being added and moved in the browser.